package com.deng.maintenance.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.maintenance.dao.IFunctionTemplate;
import com.deng.maintenance.vo.FunctionTemplateVo;

public class FunctionTemplateImpl extends SQLHelper implements
		IFunctionTemplate {

	public void addValue(FunctionTemplateVo vo) {
		try{
			Connection con = getConnect();
			ResultSet rs = queryData(con,"select case when max(id)+1 is null then 1 else max(id)+1 end as id" +
					" from FUNCTION_TEMPLATE");	
			int maxID = 0;
			if(rs.next()){
				maxID = rs.getInt("id");
			}
			
			String sql = "insert into FUNCTION_TEMPLATE " +
					"	(id,function_detail,function_description) " +
					"	values("+maxID+",'"+vo.function_detail+"','"+vo.function_description+"')";
			updateData(con,sql);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public List<FunctionTemplateVo> getAllValue() {
		try{
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,"select * from FUNCTION_TEMPLATE order by function_description");
			List<FunctionTemplateVo> list = new ArrayList<FunctionTemplateVo>();
			while(rs.next()){
				FunctionTemplateVo vo = new FunctionTemplateVo();
				vo.id = rs.getInt("id");
				vo.function_description = rs.getString("function_description");
				vo.function_detail = rs.getString("function_detail");
				list.add(vo);
			}
			return list;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public void copyTemplate(int organization_id, FunctionTemplateVo vo) {
		try{
			Connection con = getConnect();
						
			String sql = "update t_sys_stat_department " +
					"	set base_bonus_function_detail = '"
						+vo.function_detail.replace("UP_Period", "UP_Period_"+organization_id).replaceAll("'", "''")+"'" +
					"	where id = "+organization_id;
			updateData(con,sql);
			freeConnection(con);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		
		
	}

	public String copyAll(int fromOrgId, int toOrgId) {
		// TODO Auto-generated method stub
		String str = "";
		str +=copyPoint(fromOrgId, toOrgId);
		str +="。           "+copyRuleToOne(fromOrgId, toOrgId);
		return str;
	}

	public String copyRuleToOne(int fromOrgId, int toOrgId) {
		try
		{
			Connection con = getConnect();	
			
//			String sql = "update t_sys_stat_department" +
//					" set base_bonus_function_detail = " +
//					" (select replace(a.base_bonus_function_detail,'_"+fromOrgId+"(','_"+toOrgId+"(')" +
//					" from t_sys_stat_department as a where a.id = "+fromOrgId+")" +
//					" where id = "+toOrgId;
//			updateData(con,sql);
			
			//更新UNIT_PRICE_RULE_UNIT
			String sql = "delete UNIT_PRICE_RULE_UNIT where organization_id = "+toOrgId;
			updateData(con,sql);
			sql = "insert into UNIT_PRICE_RULE_UNIT" +
					" (group_id,is_mark,unit_id,type_name,value,organization_id,fan)" +
					" select group_id,is_mark,unit_id,type_name,value,"+toOrgId+",fan" +
					" from UNIT_PRICE_RULE_UNIT" +
					" where organization_id = "+fromOrgId;
			updateData(con,sql);
			
			//更新UNIT_PRICE_RULE_GROUP
			sql = "delete UNIT_PRICE_RULE_GROUP where organization_id = "+toOrgId;
			updateData(con,sql);
			sql = "insert into UNIT_PRICE_RULE_GROUP " +
					" (organization_id,name,group_id,is_mark,order_operate,is_fee) " +
					" select "+toOrgId+",name,group_id,is_mark,order_operate,is_fee" +
					" from UNIT_PRICE_RULE_GROUP where organization_id = "+fromOrgId;
			updateData(con,sql);
			
			new RuleEditImpl().saveSPForCopy(toOrgId,0,con);
			
			freeConnection(con);
			return null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "规则模板尚未设置，请检查。";
	}
	
	
	
	public String copyPoint(int fromOrgId, int toOrgId) {
		try
		{
			Connection con = getConnect();			
			String sql = "delete UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION	where organization_id = "+toOrgId;
			updateData(con,sql);
			sql = "insert into UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION" +
					" (organization_id,item_id,point,order_point,operate_point,is_important,duty_id)	" +
					" select "+toOrgId+",item_id,point,order_point,operate_point,is_important," +
					" (select duty_id from t_sys_stat_department where id = "+toOrgId+")" +
					" from UNIT_PRICE_ITEM_INCOME_VS_ORGANIZATION	where organization_id = "+fromOrgId;
			updateData(con,sql);
			freeConnection(con);	
			return null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "点数拷贝不成功，请联系管理员。";
	}
	
	public String copyRuleFromOne(int fromOrgId, String toOrgIdS) {
		try
		{
			Connection con = getConnect();
			String toOrgS[] = toOrgIdS.split(",");
			for(int i = 0; i< toOrgS.length;i++)
			{
				if( toOrgS[i].length() == 0)
				{
					continue;
				}
				int toOrgId = Integer.valueOf(toOrgS[i]);
//				String sql = "update t_sys_stat_department" +
//						" set base_bonus_function_detail = " +
//						" (select replace(a.base_bonus_function_detail,'_"+fromOrgId+"(','_"+toOrgId+"(')" +
//						" from t_sys_stat_department as a where a.id = "+fromOrgId+")" +
//						" where id = "+toOrgId;
//				updateData(con,sql);
				
				//更新UNIT_PRICE_RULE_UNIT
				String sql = "delete UNIT_PRICE_RULE_UNIT where organization_id = "+toOrgId;
				updateData(con,sql);
				sql = "insert into UNIT_PRICE_RULE_UNIT" +
						" (group_id,is_mark,unit_id,type_name,value,organization_id,fan)" +
						" select group_id,is_mark,unit_id,type_name,value,"+toOrgId+",fan" +
						" from UNIT_PRICE_RULE_UNIT" +
						" where organization_id = "+fromOrgId;
				updateData(con,sql);
				
				//更新UNIT_PRICE_RULE_GROUP
				sql = "delete UNIT_PRICE_RULE_GROUP where organization_id = "+toOrgId;
				updateData(con,sql);
				sql = "insert into UNIT_PRICE_RULE_GROUP " +
						" (organization_id,name,group_id,is_mark,order_operate,is_fee) " +
						" select "+toOrgId+",name,group_id,is_mark,order_operate,is_fee" +
						" from UNIT_PRICE_RULE_GROUP where organization_id = "+fromOrgId;
				updateData(con,sql);
				
				new RuleEditImpl().saveSPForCopy(toOrgId,0,con);
			}
			freeConnection(con);
			return null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "规则模板尚未设置，请检查。";
	}

	public String copyFormulaFromOne(int fromOrgId, String toOrgIdS) {
		try
		{
			Connection con = getConnect();
			String toOrgS[] = toOrgIdS.split(",");
			for(int i = 0; i< toOrgS.length;i++)
			{
				if( toOrgS[i].length() == 0)
				{
					continue;
				}
				int toOrgId = Integer.valueOf(toOrgS[i]);
				String sql = "update t_sys_stat_department" +
						" set base_bonus_function_detail = " +
						" (select replace(a.base_bonus_function_detail,'_"+fromOrgId+"(','_"+toOrgId+"(')" +
						" from t_sys_stat_department as a where a.id = "+fromOrgId+")" +
						" where id = "+toOrgId;
				updateData(con,sql);				
			}
			freeConnection(con);
			return null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "公式模板尚未设置，请检查。";
	}

	public String copyFormulaToOne(int fromOrgId, int toOrgId) {
		try
		{
			Connection con = getConnect();	
			
			String sql = "update t_sys_stat_department" +
					" set base_bonus_function_detail = " +
					" (select replace(a.base_bonus_function_detail,'_"+fromOrgId+"(','_"+toOrgId+"(')" +
					" from t_sys_stat_department as a where a.id = "+fromOrgId+")" +
					" where id = "+toOrgId;
			updateData(con,sql);
			
			freeConnection(con);
			return null;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return "公式模板尚未设置，请检查。";
	}

}
